﻿/// <summary>
/// @Version: 1.0
/// @Author: Patrick Weis
/// @Comment:
///     Contains all information needed to create classes and SQL-Scripts
/// @Todo: 
///     
/// </summary>


using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using Group3.MediaTool.Core.ObjectGenerator;
using System.Text;
using Group3.MediaTool.Interface;
using Group3.MediaTool.Core;
using Group3.MediaTool.Provider;
using Group3.MediaTool.Data.IO;


namespace Group3.MediaTool.Data.Generator
{
    /// <summary>
    /// Description of DataDefinitionClass.
    /// </summary>
    public class DataDefinitionClass
    {
        //String _datatypKey = "BIGINT";
        String _datatypKey = "INT";
        //String _filePath = "";
        XDocument doc;

        public DataDefinitionClass()
        {
        }

        List<IObjectDefinition> _objects = new List<IObjectDefinition>();
        public List<IObjectDefinition> Objects
        {
            get { return _objects; }
        }

        StringBuilder sb = new System.Text.StringBuilder();
        public String BackEndScript
        {
            get { return sb.ToString(); }
            set { sb.Clear(); sb.Append(value); }
        }

        public void createObjects()
        {
            doc = Group3.MediaTool.Data.IO.StaticConfig.classConfig;
            var classes = doc.Descendants("class");

            //get all classes with create flag
            var created = from c in classes
                          where c.Element("change").Value == "create"
                          select c;

            //get all classes with modified flag --> New attribute or deleted attribute
            var modified = from c in classes
                           where c.Element("change").Value == "modified"
                           select c;

            //get all classes with delete flag
            var deleted = from c in classes
                          where c.Element("change").Value == "deleted"
                          select c;

            //For all classes that will be created 
            foreach (var cls in created)
            {
                //Def. for Definition of Class with IObjectDefinition
                IObjectDefinition def = new Core.ObjectGenerator.ObjectDefinition();
                def.DataFields = new List<IFieldDefinition>();

                //Def. for Definition of SQL-Statements, First part of SQL statement
                String sqlStatementClass = "";


                foreach (String xmlFrag in Group3.MediaTool.Data.IO.StaticConfig.fixedAttributesInXML)
                {
                    XElement xmlFragmentCreate =
                        XDocument.Parse(xmlFrag).Elements().First();

                    var check = from e in cls.Elements("attribute")
                                where e.Element("name").Value == xmlFragmentCreate.Element("name").Value
                                select e;
                    if (check.Count() == 0)
                    {
                        cls.Add(xmlFragmentCreate);
                    }
                }

                addToMainScript("");
                //get all attributes and references
                var attributes = cls.Descendants("attribute");
                var references = cls.Descendants("refAttribute");

                //Set Classname
                def.OrmName = cls.Element("name").Value;
                //set datafields valid for create class and modified attributes
                def.DataFields = createAttrDefinition(attributes, references);
                _objects.Add(def);

                //create SQL-Script
                sqlStatementClass = String.Concat("CREATE TABLE ", cls.Element("name").Value.ToUpper(), " ( ID ", _datatypKey, " IDENTITY(1,1) NOT NULL PRIMARY KEY, CREATED DATETIME, MODIFIED DATETIME, ");
                foreach (var attr in attributes)
                {
                    if (!(attr.Element("name").Value.ToUpper() == "ID" || attr.Element("name").Value.ToUpper() == "MODIFIED" || attr.Element("name").Value.ToUpper() == "CREATED"))
                    {
                        //update SQL for the new attribute 
                        sqlStatementClass = String.Concat(sqlStatementClass, attr.Element("name").Value.ToUpper()
                                            , " ", attr.Element("type").Value.ToUpper(), " ", attr.Element("nullable").Value.ToUpper(), ", ");
                    }
                }
                sqlStatementClass = sqlStatementClass.Remove(sqlStatementClass.Length - 2, 2) + " );";
                //Set SQL script for DataDefinition.
                cls.Element("BackEndDef").Value = sqlStatementClass;
                addToMainScript(sqlStatementClass);
                sqlStatementClass = "";


                foreach (var refA in references)
                {
                    //update XML with BackendScripts
                    if (refA.Element("BackEndDef").Value != "done")
                    {
                        createRefSQL(refA, cls, classes);
                    }
                }
                //Set change-attribute to empty, that no more changes will be made
                cls.Element("change").Value = "";
            }

            //For all classes that will be modified 
            foreach (var cls in modified)
            {
                //Def. for Definition of Class with IObjectDefinition
                IObjectDefinition def = new Core.ObjectGenerator.ObjectDefinition();
                def.DataFields = new List<IFieldDefinition>();

                //Def. for Definition of SQL-Statements, First part of SQL statement
                String sqlStatementClass = "";
                String sqlStatementClassAdd = "";
                String sqlStatementClassDel = "";
                //String sqlStatementRefAdd = "";
                String sqlStatementRefDel = "";

                //get all attributes and references
                var attributes = cls.Descendants("attribute");
                var references = cls.Descendants("refAttribute");

                //Set Classname
                def.OrmName = cls.Element("name").Value;
                //set datafields valid for create class and modified attributes
                def.DataFields = createAttrDefinition(attributes, references);
                _objects.Add(def);

                //Attribute based changes 
                sqlStatementClass = "";
                bool _ColumnAdded = false;
                sqlStatementClassAdd = String.Concat("ALTER TABLE ", cls.Element("name").Value.ToUpper(), " ADD ");
                bool _ColumnDeleted = false;
                sqlStatementClassDel = String.Concat("ALTER TABLE ", cls.Element("name").Value.ToUpper(), " DROP ");
                var modAdd = from m in attributes
                             where m.Element("change").Value == "add"
                             select m;

                var modDel = from m in attributes
                             where m.Element("change").Value == "delete"
                             select m;


                foreach (var a in modAdd)
                {
                    if (modAdd.Count() > 1 && _ColumnAdded == false)
                    {
                        sqlStatementClassAdd = String.Concat(sqlStatementClassAdd, "(");
                    }
                    _ColumnAdded = true;
                    sqlStatementClassAdd = String.Concat(sqlStatementClassAdd, a.Element("name").Value.ToUpper(), " ", a.Element("type").Value.ToUpper(), ", ");
                    //Set change-attribute of Attributes to empty
                    a.Element("change").Value = "";
                }

                if (modAdd.Count() > 1)
                {
                    sqlStatementClassAdd = sqlStatementClassAdd.Remove(sqlStatementClassAdd.Length - 2, 2) + " );";
                }
                else
                {
                    sqlStatementClassAdd = sqlStatementClassAdd.Remove(sqlStatementClassAdd.Length - 2, 2) + " ;";
                }

                foreach (var d in modDel)
                {
                    if (modDel.Count() > 1 && _ColumnDeleted == false)
                    {
                        sqlStatementClassDel = String.Concat(sqlStatementClassDel, "(");
                    }
                    _ColumnDeleted = true;
                    sqlStatementClassDel = String.Concat(sqlStatementClassDel, d.Element("name").Value.ToUpper(), ", ");
                    
                    //Remove attriute from XML
                    d.Remove();
                }

                if (modDel.Count() > 1)
                {
                    sqlStatementClassDel = sqlStatementClassDel.Remove(sqlStatementClassDel.Length - 2, 2) + " );";
                }
                else
                {
                    sqlStatementClassDel = sqlStatementClassDel.Remove(sqlStatementClassDel.Length - 2, 2) + " ;";
                }

                if (_ColumnAdded)
                {
                    sqlStatementClass = String.Concat(sqlStatementClassAdd, Environment.NewLine);
                }
                if (_ColumnDeleted)
                {
                    sqlStatementClass = String.Concat(sqlStatementClassDel, Environment.NewLine);
                }

                cls.Element("BackEndDef").Value = sqlStatementClass;
                addToMainScript(sqlStatementClass);

                //reference based changes 		
                //sqlStatementRefAdd = "";
                sqlStatementRefDel = "DROP TABLE";
                var modrefAdd = from m in references
                                where m.Element("change").Value == "add"
                                select m;

                var modrefDel = from m in references
                                where m.Element("change").Value == "delete"
                                select m;

                foreach (var a in modrefAdd)
                {
                    createRefSQL(a, cls, classes);
                }

                foreach (var d in modrefDel)
                {
                    sqlStatementRefDel = String.Concat("DROP TABLE ", d.Element("refTable").Value.ToUpper(), ";");
                    d.Element("BackEndDef").Value = sqlStatementRefDel;
                    addToMainScript(sqlStatementRefDel);
                    //Set change-attribute of Attributes to empty
                    d.Remove();
                }
                //Set change-attribute of class to empty, that no more changes will be made
                cls.Element("change").Value = "";
                
                
            }

            //For all classes that will be deleted 
            foreach (var cls in deleted)
            {
                String sqlStatementClass = "";
                sqlStatementClass = String.Concat("DROP TABLE ", cls.Element("name").Value.ToUpper());
                cls.Element("BackEndDef").Value = sqlStatementClass;
                addToMainScript(sqlStatementClass);
                //Set change-attribute to empty, that no more changes will be made
                cls.Element("change").Value = "";
            }
            doc.Element("definition").Element("script").Value = "";
            doc.Element("definition").Element("script").Value = BackEndScript;
            addToMainScript("");

            if (StaticConfig.Database == e_DataStoreProvider.MSSQLServer)
            {
                createDatabaseTables();
            }

        }

        private void createRefSQL(XElement refA, XElement cls, IEnumerable<XElement> classes)
        {
            String sqlStatementRef = "";
            sqlStatementRef = String.Concat("CREATE TABLE " + refA.Element("refTable").Value.ToUpper() ,
                                " ( ID " , _datatypKey , " IDENTITY(1,1) NOT NULL PRIMARY KEY, " , refA.Element("refClass").Value.ToUpper() , " " , _datatypKey
                                 , " NOT NULL, " , cls.Element("name").Value.ToUpper() , " " , _datatypKey , " NOT NULL)");

            refA.Element("BackEndDef").Value = sqlStatementRef;
            var refClass = from c in classes
                           where c.Element("name").Value == refA.Element("refClass").Value
                           select c;

            var refClassAttr = from r in refClass.Descendants("refAttribute")
                               where r.Element("refTable").Value == refA.Element("refTable").Value
                               select r;

            refClassAttr.First().Element("BackEndDef").Value = "done";

            addToMainScript(sqlStatementRef);
            sqlStatementRef = "";
        }

        private List<IFieldDefinition> createAttrDefinition(IEnumerable<XElement> attributes, IEnumerable<XElement> references)
        {
            List<IFieldDefinition> fields = new List<IFieldDefinition>();

            foreach (var attr in attributes)
            {
                //create attribute Definition
                IFieldDefinition fdef = new FieldDefinition();
                fdef.FieldName = attr.Element("name").Value;
                fdef.ValueType = mapValueToDBType(attr.Element("type").Value);
                if (attr.Element("key").Value == "1") { fdef.IsKey = true; } else { fdef.IsKey = false; }
                //Add attribute Definition to class definition
                fields.Add(fdef);
            }
            foreach (var refA in references)
            {
                //create Field definition for the references
                IFieldDefinition fdef = new FieldDefinition();
                fdef.FieldName = refA.Element("name").Value;
                fdef.ValueType = e_ValueType.Relation;
                fdef.IsKey = false;
                fields.Add(fdef);
            }
            return fields;
        }

        private void addToMainScript(string s)
        {
            DataDefinitionIO ddio = new DataDefinitionIO();

            if (!String.IsNullOrEmpty(s)) { sb.AppendLine(s); }
            ddio.writeXMLFile(this.doc);
        }

        private e_ValueType mapValueToDBType(string dbType)
        {
            String[] getInfo = dbType.Split('(');

            switch (getInfo[0].ToLower())
            {
                case "bit":
                    return e_ValueType.Boolean;
                case "real":
                case "float":
                    return e_ValueType.Decimal;
                case "tinyint":
                case "smallint":
                case "int":
                case "bigint":
                case "numeric":
                    return e_ValueType.Integer;
                case "ref":
                    return e_ValueType.Relation;
                case "binary":
                case "varbinary":
                case "image":
                    return e_ValueType.Stream;
                case "char":
                case "varchar":
                case "text":
                case "nchar":
                case "nvarchar":
                case "ntext":
                    return e_ValueType.String;
                case "datetime":
                case "smalldatetime":
                case "date":
                case "time":
                case "datetime2":
                case "datetimeoffset":
                    return e_ValueType.String;
                case "smallmoney":
                case "money":
                    return e_ValueType.String;
                default:
                    return e_ValueType.Unknown;

            }
        }

        private void createDatabaseTables()
        {
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            bool test = sqlConn.runCommand(this.BackEndScript);
        }


    }
}

